USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetActivityDetails')
	BEGIN
		DROP  Procedure  dbo.GetActivityDetails
	END
GO

CREATE Procedure [dbo].[GetActivityDetails]
	@PKID		nvarchar(100) = NULL
,	@OWNERID	varchar(10)	  = '1'
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@accounts		varchar(max)
	,	@contacts		varchar(max)
	,	@allUsers		varchar(max)
	,	@activitytype	varchar(max)
	,	@activitystatus	varchar(max)
	,	@relations		varchar(max)

	SELECT	@sql = ''
		,	@accounts		= dbo.GetAccountsXml(@OWNERID)
		,	@contacts		= dbo.GetContactsXml('')
		,	@allUsers		= dbo.GetAllUsersXml()
		,	@activityType	= dbo.GetActivityTypeXml()
		,	@activitystatus	= dbo.GetStatusXml('Activity')
		,	@relations		= dbo.GetRelationsXML(@PKID)


		


	;WITH TAct AS (
		SELECT 
			at.ActivityTaskID		AS ActivityID
		,	at.Description			AS ActivityName
		,	at.ActivityTaskGuid		AS ActivityGUID	
		,	at.ActivityType			AS ActivityTypeID
		,	at.Status				as ActivityStatusID
		,	at.StartDate			as ActivityStartDate
		,	at.EndDate				as ActivityEndDate
		FROM TR_ACTIVITYTASK as at WITH (NOLOCK)
		WHERE
			CAST(at.ActivityTaskGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'						AS ActivityID
		,	''						AS ActivityName
		,	NEWID()					AS ActivityGUID	
		,	'0'						AS ActivityTypeID
		,	'0'						as ActivityStatusID
		,	GETDATE()				as ActivityStartDate
		,	dateadd(hh,1,GETDATE())	as ActivityEndDate
		WHERE
			ISNULL(@PKID, '') = ''
	), TActNotes AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN TAct as a with (NOLOCK)
			on a.ActivityGUID = n.SourceGUID
	), FinalXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @accounts as Details
		UNION
		SELECT 2.2 as ID, @contacts as Details
		UNION
		SELECT 2.5 as ID, @allUsers as Details
		UNION
		SELECT 2.6 as ID, @activityType as Details
		UNION
		SELECT 2.7 as ID, @activityStatus as Details
		UNION
		SELECT 2.8 as ID, @relations as Details
		UNION
		SELECT
			3 AS ID, 
			'<PKID><![CDATA[' + ISNULL(CAST(A.ActivityGUID AS VARCHAR(40)), '') + ']]></PKID>'
		+	'<ACTIVITYID><![CDATA['+ ISNULL( CAST(A.ACTIVITYID AS VARCHAR),'') +']]></ACTIVITYID>'
		+	'<ACTIVITYGUID><![CDATA[' + ISNULL(CAST(A.ActivityGUID AS VARCHAR(40)), '') + ']]></ACTIVITYGUID>'
		+	'<ACTIVITYNAME><![CDATA['+ ISNULL(A.ActivityName,'') +']]></ACTIVITYNAME>'
		+	'<ACTIVITYTYPEID><![CDATA['+ ISNULL( CAST(A.ACTIVITYTYPEID AS VARCHAR),'') +']]></ACTIVITYTYPEID>'
		+	'<ACTIVITYSTATUSID><![CDATA['+ ISNULL( CAST(A.ACTIVITYSTATUSID AS VARCHAR),'') +']]></ACTIVITYSTATUSID>'
		+	'<STARTDATE><![CDATA['+ CONVERT(VARCHAR(10), ISNULL(ActivityStartDate, GETDATE()), 101)+']]></STARTDATE>'
		+	'<STARTTIME><![CDATA['+CONVERT(VARCHAR(10), ISNULL(ActivityStartDate, GETDATE()), 108)+']]></STARTTIME>'
		+	'<ENDDATE><![CDATA['+CONVERT(VARCHAR(10), ISNULL(ActivityEndDate, GETDATE()), 101)+']]></ENDDATE>'
		+	'<ENDTIME><![CDATA['+CONVERT(VARCHAR(10), ISNULL(ActivityEndDate, GETDATE()), 108)+']]></ENDTIME>'
		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
					AS Details
		FROM TAct as A
		LEFT OUTER JOIN TActNotes as n
			on n.SourceGUID = A.ActivityGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM FinalXML
	Order by ID

	select @sql	AS Detail
END
GO
